﻿using System;
using System.Collections.Generic;
using System.Text;
using Maticsoft.DBUtility;
using System.Collections;
using System.Data;
using System.Web;

namespace Comm
{
    public class DataToExcel
    {
        public static void Export(string[] fileName, string[] title, string tableName, string sheetName, string strWhere, string sort)
        {
            string sql = "select ";
            if (fileName == null || fileName.Length < 1)
            {
                sql += "* ";
            }
            else
            {
                foreach (string key in fileName)
                {
                    sql += key + ",";
                }
                sql = System.Text.RegularExpressions.Regex.Replace(sql, ",$", " ");
            }
            sql += "from " + tableName;
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += " where " + strWhere;
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sql += " order by " + sort;
            }
            
            //获取需要导出的数据
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
            NPOI.SS.UserModel.Row row = sheet.CreateRow(0);

            //设置列名称和数据
            if (title == null || title.Length < 1)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            else
            {
                for (int i = 0; i < title.Length; i++)
                {
                    row.CreateCell(i).SetCellValue(title[i].ToString());
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < fileName.Length; j++)
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
                }
            }
            
            
            


            //写入到客户端   
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Export_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"));
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();   
        }

    }
}
